
Create PROC [dbo].[STILoadDrugInvoiceForSendToTaxSystem]
    @Startdate VARCHAR(10),
    @EndDate VARCHAR(10),
    @StartTime VARCHAR(10),
    @EndTime VARCHAR(10),
    @TypeFactor TINYINT,
    @ComputerName NVARCHAR(MAX),
    @InvoiceNumber VARCHAR(10)
AS
BEGIN
IF @InvoiceNumber = 0 
  SET @InvoiceNumber=NULL

    
    IF EXISTS
    (
        SELECT *
        FROM [tempdb].dbo.sysobjects
        WHERE name = '##CacheFactor'
              AND xtype = 'U'
    )
    BEGIN
        DELETE FROM [tempdb].dbo.##CacheFactor
        WHERE ComputerName = @ComputerName;
    END;
    ELSE
    BEGIN
        CREATE TABLE ##CacheFactor
        (
            Id BIGINT,
            InvoiceType TINYINT,
            InvoiceNumber VARCHAR(10) COLLATE Arabic_CI_AS,
            SenderName NVARCHAR(35) COLLATE Arabic_CI_AS,
            RecipientName NVARCHAR(35) COLLATE Arabic_CI_AS,
            TotalPrice MONEY,
            FactorDate VARCHAR(10) COLLATE Arabic_CI_AS,
            TimeFactor VARCHAR(5) COLLATE Arabic_CI_AS,
            IsSend BIT DEFAULT (1),
            DeductionFactor MONEY,
            IncrementFactor MONEY,
            ReceiptAndPaymentPrice MONEY,
            liquidationFactor MONEY,
            ComputerName NVARCHAR(MAX) COLLATE Arabic_CI_AS
        );
    END;
--////////////////////////////////////TDrugh
 DECLARE @TDrugH TABLE
    (
        Id_havaleh BIGINT,
        Kind_flag CHAR(1),
        InvoiceNumber VARCHAR(8),
        Trans_Name NVARCHAR(35),
        Recive_Name NVARCHAR(35),
        TotalPrice MONEY,
        FactorDate VARCHAR(10),
        TimeFactor VARCHAR(5),
		ReceiptAndPaymentPrice money
    );
INSERT INTO @TDrugH

	SELECT Id_Havaleh,
			
           Kind_Flag,
           CASE
               WHEN Kind_Flag IN ( 1) THEN
                   Nu_Forosh
               WHEN Kind_Flag IN (2 ) THEN
                   Nu_Bargasht_Forosh
           END AS InvoiceNumber,
           Trans_Name,
           Resive_Name,
           ( ISNULL(TotPriceTaghizat, 0) + ISNULL(TotPriceDiffer, 0) + ISNULL(TotPriceAzad, 0) 
		   + ISNULL(TotPriceKala, 0) + ISNULL(TotPriceOTC, 0) + ISNULL(TotPriceBimar, 0) 
                           + ISNULL(TotPriceSazman, 0) )
            AS TotalPrice,
           dbo.MiladiToShamsi( CONVERT(VARCHAR(10), Tarikh, 111) )AS FactorDate,
           CONVERT(VARCHAR(5), Tarikh, 108)TimeFactor, ISNULL(TotPriceDaryafti ,0 )
  
    FROM Drugh WITH (NOLOCK)
     
    WHERE (Kind_Flag = @TypeFactor) 
	AND (kind_Save NOT IN ( 4,3))
          AND CONVERT(VARCHAR(10), Tarikh, 111)
          BETWEEN (@Startdate) AND (@EndDate)
          AND CONVERT(VARCHAR(10), Tarikh, 108)
          BETWEEN @StartTime AND @EndTime
 
 AND (NOT EXISTS
    (
        SELECT *
        FROM dbo.STITransaction WITH (NOLOCK)
        WHERE InvoiceId = dbo.drugh.Id_Havaleh AND type IN (0,1) 
    ))

--///////////////////////////////////ToverUnder
DECLARE @TOver_UnderDrug TABLE
    (
        id_havaleh BIGINT,
        IncrementFactor MONEY,
		DeductionFactor MONEY
    );
	 INSERT INTO @TOver_UnderDrug
 SELECT Over_Under_Factor_Drug.Id_Havaleh,
                   SUM(   CASE
                              WHEN Type = 1 THEN
                                  ROUND(Price, 0)
                              ELSE
                                  0
                          END
                      ) IncrementFactor,
                   SUM(   CASE
                              WHEN Type = 0 THEN
                                  ROUND(Price, 0)
                              ELSE
                                  0
                          END
                      ) DeductionFactor
            FROM Over_Under_Factor_Drug WITH (NOLOCK)
                INNER JOIN  
        (
            SELECT Drugh.Id_Havaleh
            FROM Drugh WITH (NOLOCK)
            WHERE Kind_Flag = @TypeFactor
                  AND CONVERT(VARCHAR(10), Tarikh, 111)
                  BETWEEN (@Startdate) AND (@EndDate)
                  AND CONVERT(VARCHAR(10), Tarikh, 108)
                  BETWEEN @StartTime AND @EndTime
        ) AS tb1
            ON tb1.Id_Havaleh = Over_Under_Factor_Drug.Id_Havaleh
    WHERE NOT EXISTS
    (
        SELECT *
        FROM dbo.STITransaction WITH (NOLOCK)
        WHERE InvoiceId = Over_Under_Factor_Drug.Id_Havaleh
              AND Type = CASE
                             WHEN @TypeFactor IN ( 1) THEN
                                 0
                             WHEN @TypeFactor IN ( 2 ) THEN
                                 1
                         END
    )

            GROUP BY  Over_Under_Factor_Drug.Id_Havaleh

--/////////////////////////////////////////////
    INSERT INTO ##CacheFactor
    (
        Id,
        InvoiceType,
        InvoiceNumber,
        SenderName,
        RecipientName,
        TotalPrice,
        FactorDate,
        TimeFactor,
        IsSend,
		DeductionFactor,
		IncrementFactor,
		ReceiptAndPaymentPrice,
		liquidationFactor,
        ComputerName
    )
  SELECT Tbdrugh.Id_Havaleh,Tbdrugh.Kind_Flag,Tbdrugh.InvoiceNumber,Tbdrugh.Trans_Name,Tbdrugh.Recive_Name,
Tbdrugh.TotalPrice,Tbdrugh.FactorDate,Tbdrugh.TimeFactor,0
,TbOver_UnderDrug.DeductionFactor,TbOver_UnderDrug.IncrementFactor,ReceiptAndPaymentPrice, (Tbdrugh.TotalPrice + ISNULL(IncrementFactor - DeductionFactor, 0))
           - ISNULL(ReceiptAndPaymentPrice, 0) liquidationFactor, @ComputerName AS ComputerName 
		   FROM (
		   SELECT * FROM @TDrugH  
		   WHERE InvoiceNumber = ISNULL(@InvoiceNumber, InvoiceNumber) 
		   ) AS Tbdrugh
        LEFT OUTER JOIN
        (SELECT * FROM @TOver_UnderDrug ) AS TbOver_UnderDrug
            ON TbOver_UnderDrug.id_havaleh = Tbdrugh.id_havaleh
			 

END;
